﻿' This sample code is courtesy of http://www.gotreportviewer.com

Imports System.Collections.Generic
Imports System.Text
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.Reporting.WebForms

Namespace RdlViewer
    ''' <summary>
    ''' Think of this class as a "Reflector for RDL". It digs into the XML refinition of the RDL and
    ''' extracts useful information.
    ''' </summary>
    Public Class clsReportInfo
        Private m_rdlFilename As String
        Private m_dataSourceDictionary As Dictionary(Of String, DataSourceInfo)
        Private m_dataSetDictionary As Dictionary(Of String, DataSetInfo)
        Private m_reportParmList As List(Of ReportParmInfo)

        Public Sub New(rdlFilename As String)
            Me.m_rdlFilename = rdlFilename
        End Sub

        Public ReadOnly Property FileName As String
            Get
                Return m_rdlFilename
            End Get
        End Property

        Public ReadOnly Property DataSources As Dictionary(Of String, DataSourceInfo)
            Get
                Return m_dataSourceDictionary
            End Get
        End Property

        Public ReadOnly Property DataSets As Dictionary(Of String, DataSetInfo)
            Get
                Return m_dataSetDictionary
            End Get
        End Property

        Public ReadOnly Property ReportParameters As List(Of ReportParmInfo)
            Get
                Return m_reportParmList
            End Get
        End Property

        Private Sub ExtractReferencedDataSourceInfo(dataSourceName As String, dataSourceReference As String)
            Dim rdsDocument As New XmlDocument()
            Dim rdsFilename As String = Path.GetDirectoryName(m_rdlFilename) & "\" & dataSourceReference & ".rds"
            rdsDocument.Load(rdsFilename)

            Dim connectionPropsNode As XmlNode = rdsDocument.SelectSingleNode("RptDataSource/ConnectionProperties")
            Dim connectStringNode As XmlNode = connectionPropsNode.SelectSingleNode("ConnectString")
            Dim connectString As String = connectStringNode.InnerText
            m_dataSourceDictionary.Add(dataSourceName, New DataSourceInfo(connectString))
        End Sub

        Private Sub ExtractDataSourceInfo(rdlDocument As XmlDocument, nsmgr As XmlNamespaceManager)
            m_dataSourceDictionary = New Dictionary(Of String, DataSourceInfo)()

            Dim dataSourcesNode As XmlNode = rdlDocument.SelectSingleNode("r:Report/r:DataSources", nsmgr)
            If dataSourcesNode Is Nothing Then
                Return
            End If
            Dim dataSourceList As XmlNodeList = dataSourcesNode.SelectNodes("r:DataSource", nsmgr)
            For Each dataSourceNode As XmlNode In dataSourceList
                Dim dataSourceName As String = dataSourceNode.Attributes.GetNamedItem("Name").InnerText
                Dim connectionPropsNode As XmlNode = dataSourceNode.SelectSingleNode("r:ConnectionProperties", nsmgr)
                If connectionPropsNode Is Nothing Then
                    Dim dataSourceReferenceNode As XmlNode = dataSourceNode.SelectSingleNode("r:DataSourceReference", nsmgr)
                    Dim dataSourceReference As String = dataSourceReferenceNode.InnerText
                    ExtractReferencedDataSourceInfo(dataSourceName, dataSourceReference)
                Else
                    Dim connectStringNode As XmlNode = connectionPropsNode.SelectSingleNode("r:ConnectString", nsmgr)
                    Dim connectString As String = connectStringNode.InnerText
                    m_dataSourceDictionary.Add(dataSourceName, New DataSourceInfo(connectString))
                End If
            Next
        End Sub

        Private Sub ExtractDataSetInfo(rdlDocument As XmlDocument, nsmgr As XmlNamespaceManager)
            m_dataSetDictionary = New Dictionary(Of String, DataSetInfo)()

            Dim dataSetsNode As XmlNode = rdlDocument.SelectSingleNode("r:Report/r:DataSets", nsmgr)
            If dataSetsNode Is Nothing Then
                Return
            End If
            Dim dataSetList As XmlNodeList = dataSetsNode.SelectNodes("r:DataSet", nsmgr)
            For Each dataSetNode As XmlNode In dataSetList
                Dim dataSetName As String = dataSetNode.Attributes.GetNamedItem("Name").InnerText
                Dim queryNode As XmlNode = dataSetNode.SelectSingleNode("r:Query", nsmgr)
                Dim dataSourceNameNode As XmlNode = queryNode.SelectSingleNode("r:DataSourceName", nsmgr)
                Dim dataSourceName As String = dataSourceNameNode.InnerText
                Dim commandTextNode As XmlNode = queryNode.SelectSingleNode("r:CommandText", nsmgr)
                Dim commandText As String = commandTextNode.InnerText

                Dim parametersNode As XmlNode = queryNode.SelectSingleNode("r:QueryParameters", nsmgr)
                Dim queryParameters As IList(Of QueryParameterInfo) = Nothing
                If parametersNode IsNot Nothing Then
                    queryParameters = New List(Of QueryParameterInfo)()
                    Dim parameterList As XmlNodeList = parametersNode.SelectNodes("r:QueryParameter", nsmgr)
                    For Each parameterNode As XmlNode In parameterList
                        Dim parameterName As String = parameterNode.Attributes.GetNamedItem("Name").InnerText
                        Dim valueNode As XmlNode = parameterNode.SelectSingleNode("r:Value", nsmgr)
                        Dim valueExpression As String = valueNode.InnerText
                        Dim queryParameterInfo As New QueryParameterInfo(parameterName, valueExpression)
                        queryParameters.Add(queryParameterInfo)
                    Next
                End If

                Dim filtersNode As XmlNode = dataSetNode.SelectSingleNode("r:Filters", nsmgr)
                Dim queryFilters As IList(Of QueryFilterInfo) = Nothing
                If filtersNode IsNot Nothing Then
                    queryFilters = New List(Of QueryFilterInfo)
                    Dim filterList As XmlNodeList = filtersNode.SelectNodes("r:Filter", nsmgr)
                    For Each filterNode As XmlNode In filterList
                        Dim expressionNode As XmlNode = filterNode.SelectSingleNode("r:FilterExpression", nsmgr)
                        Dim expression As String = expressionNode.InnerText
                        Dim operatorNode As XmlNode = filterNode.SelectSingleNode("r:Operator", nsmgr)
                        Dim [operator] As String = operatorNode.InnerText
                        Dim queryfilterInfo As New QueryFilterInfo(expression, [operator])
                        Dim valueNode As XmlNode = filterNode.SelectSingleNode("r:FilterValues", nsmgr)
                        Dim valueList As XmlNodeList = valueNode.SelectNodes("r:FilterValue", nsmgr)
                        queryfilterInfo.values = New List(Of String)
                        For Each valNode As XmlNode In valueList
                            queryfilterInfo.values.Add(valNode.InnerText)
                        Next
                        queryFilters.Add(queryfilterInfo)
                    Next
                End If

                Dim dataSetInfo As New DataSetInfo(dataSourceName, commandText)
                dataSetInfo.queryParameters = queryParameters
                dataSetInfo.queryFilters = queryFilters
                m_dataSetDictionary.Add(dataSetName, dataSetInfo)
            Next
        End Sub

        Private Sub ExtractReportParmInfo(rdldocument As XmlDocument, nsmgr As XmlNamespaceManager)
            m_reportParmList = New List(Of ReportParmInfo)()

            Dim reportparmsnode As XmlNode = rdldocument.SelectSingleNode("r:Report/r:ReportParameters", nsmgr)
            If reportparmsnode Is Nothing Then
                Return
            End If
            Dim reportparmlist As XmlNodeList = reportparmsnode.SelectNodes("r:ReportParameter", nsmgr)
            For Each reportparmnode As XmlNode In reportparmlist

                Dim reportparmname As String = reportparmnode.Attributes.GetNamedItem("Name").InnerText

                Dim datatype As String = reportparmnode.SelectSingleNode("r:DataType", nsmgr).InnerText

                Dim multivaluenode As XmlNode = reportparmnode.SelectSingleNode("r:MultiValue", nsmgr)
                Dim multivalue As String = False
                If multivaluenode IsNot Nothing Then multivalue = reportparmnode.SelectSingleNode("r:MultiValue", nsmgr).InnerText

                Dim lstdefaults As New List(Of String)
                Dim defaultvaluenode As XmlNode = reportparmnode.SelectSingleNode("r:DefaultValue", nsmgr)
                If defaultvaluenode IsNot Nothing Then
                    Dim defaultvaluelist As XmlNodeList = defaultvaluenode.SelectNodes("r:Values", nsmgr)
                    For Each valuenode As XmlNode In defaultvaluelist
                        lstdefaults.Add(valuenode.InnerText)
                    Next
                ElseIf reportparmname.ToLower = "mindate" Then
                    ''for ACP, want the first date to be the beginning of the current fiscal year (October 1)
                    'Dim mindate As Date = New Date(Now.AddMonths(-10).Year, 10, 1)
                    '6/15/15: for aprobados reports, set defaults to prior month
                    Dim mindate As Date = New Date(Now.AddMonths(-1).Year, Now.AddMonths(-1).Month, 1)
                    lstdefaults.Add(mindate.ToString)
                ElseIf reportparmname.ToLower = "maxdate" Then
                    lstdefaults.Add(New Date(Now.Year, Now.Month, 1).AddDays(-1))
                End If

                Dim prompt As String = reportparmnode.SelectSingleNode("r:Prompt", nsmgr).InnerText

                Dim validvaluesnode As XmlNode = reportparmnode.SelectSingleNode("r:ValidValues", nsmgr)

                Dim lstdataset As New List(Of ParmDatasetInfo)
                Dim dictparameter As New Generic.Dictionary(Of String, String)

                If validvaluesnode IsNot Nothing Then
                    Dim datasetlist As XmlNodeList = validvaluesnode.SelectNodes("r:DataSetReference", nsmgr)
                    For Each datasetnode As XmlNode In datasetlist
                        Dim datasetname As String = datasetnode.SelectSingleNode("r:DataSetName", nsmgr).InnerText
                        Dim valuefield As String = datasetnode.SelectSingleNode("r:ValueField", nsmgr).InnerText
                        Dim labelfield As String = datasetnode.SelectSingleNode("r:LabelField", nsmgr).InnerText
                        lstdataset.Add(New ParmDatasetInfo(datasetname, valuefield, labelfield))
                    Next

                    Dim parametervaluesnode As XmlNode = validvaluesnode.SelectSingleNode("r:ParameterValues", nsmgr)
                    If parametervaluesnode IsNot Nothing Then
                        Dim parameterlist As XmlNodeList = parametervaluesnode.SelectNodes("r:ParameterValue", nsmgr)
                        For Each parameternode As XmlNode In parameterlist
                            Dim value As String = parameternode.SelectSingleNode("r:Value", nsmgr).InnerText
                            Dim label As String = parameternode.SelectSingleNode("r:Label", nsmgr).InnerText
                            dictparameter.Add(value, label)
                        Next
                    End If
                End If

                Dim reportparminfo As New ReportParmInfo(reportparmname, prompt, datatype, lstdefaults, lstdataset, multivalue, dictparameter)
                m_reportParmList.Add(reportparminfo)
            Next
        End Sub

        Public Sub Initialize()
            Dim rdlDocument As New XmlDocument()
            rdlDocument.Load(m_rdlFilename)

            Dim root As XmlElement = rdlDocument.DocumentElement

            Dim nsmgr As New XmlNamespaceManager(rdlDocument.NameTable)
            nsmgr.AddNamespace("r", root.NamespaceURI)
            nsmgr.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")

            ExtractDataSourceInfo(rdlDocument, nsmgr)
            ExtractDataSetInfo(rdlDocument, nsmgr)
            ExtractReportParmInfo(rdlDocument, nsmgr)
        End Sub

        Public Function GetData(dataSetName As String, reportParams As ReportParameterInfoCollection) As DataTable
            Dim dataSetInfo As DataSetInfo = m_dataSetDictionary(dataSetName)
            If dataSetInfo.dataTable Is Nothing Then
                Dim dataSourceInfo As DataSourceInfo = m_dataSourceDictionary(dataSetInfo.dataSourceName)
                If dataSourceInfo.connection Is Nothing Then
                    'Dim dialog As New ConnectionDialog(dataSetInfo.dataSourceName, dataSourceInfo.connectString)
                    'If dialog.ShowDialog() = DialogResult.Cancel Then
                    '    Return Nothing
                    'Else
                    '    dataSourceInfo.connection = dialog.connection
                    'End If
                End If
                Dim command As SqlCommand = dataSourceInfo.connection.CreateCommand()
                command.CommandText = dataSetInfo.query
                If dataSetInfo.queryParameters IsNot Nothing Then
                    For Each queryParameterInfo As QueryParameterInfo In dataSetInfo.queryParameters
                        Dim valueExpression As String = queryParameterInfo.valueExpression
                        Const starting As String = "=Parameters!"
                        Const ending As String = ".Value"
                        If Not valueExpression.StartsWith(starting) OrElse Not valueExpression.EndsWith(ending) Then
                            Throw New Exception("Can't parse query parameter expression: " & valueExpression)
                        End If
                        Dim parameterNameLen As Integer = valueExpression.Length - starting.Length - ending.Length
                        Dim parameterName As String = valueExpression.Substring(starting.Length, parameterNameLen)
                        Dim reportParameterValue As String = reportParams(parameterName).Values(0)

                        If reportParameterValue Is Nothing Then
                            Throw New Exception("Report parameter " & parameterName & " has no value set")
                        End If

                        command.Parameters.AddWithValue(queryParameterInfo.parameterName, reportParameterValue)
                    Next
                End If

                Dim reader As SqlDataReader = command.ExecuteReader()
                Dim dataTable As New DataTable()
                dataTable.Load(reader)
                reader.Close()

                dataSetInfo.dataTable = dataTable
            End If
            Return dataSetInfo.dataTable
        End Function

        ' ''' <summary>
        ' ''' Retrieve data from WRDB into datatable specified by the indicated dataset; if getting records for temp data table, add Selected_By clause
        ' ''' </summary>
        ' ''' <param name="dataSetName"></param>
        ' ''' <param name="reportParams"></param>
        ' ''' <param name="Project"></param>
        ' ''' <returns></returns>
        ' ''' <remarks></remarks>
        'Public Function GetData(dataSetName As String, reportParams As ReportParameterInfoCollection, Project As WRDB.Project.clsProject, ShowAllOwnerRecords As Boolean) As DataTable
        '    Dim dataSetInfo As DataSetInfo = m_dataSetDictionary(dataSetName)
        '    If dataSetInfo.dataTable Is Nothing Then
        '        Dim dataSourceInfo As DataSourceInfo = m_dataSourceDictionary(dataSetInfo.dataSourceName)
        '        If dataSourceInfo.connection Is Nothing Then
        '            dataSourceInfo.connection = Project.DB.Connection
        '        End If
        '        Project.DB.OpenConnection()
        '        Dim command As Common.DbCommand = dataSourceInfo.connection.CreateCommand()
        '        command.CommandText = dataSetInfo.query

        '        'see if getting data from temp table; if so automatically revise the statement to only query this user's records

        '        Dim lstSQL As New Generic.List(Of String)
        '        lstSQL.AddRange(command.CommandText.ToUpper.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries))
        '        If lstSQL.Contains(WRDB.Project.clsProject.TempData.ToUpper) AndAlso Not ShowAllOwnerRecords Then
        '            If lstSQL.Contains("WHERE") Then
        '                command.CommandText &= " AND "
        '            Else
        '                command.CommandText &= " WHERE "
        '            End If
        '            command.CommandText &= String.Format("Selected_By='{0}'", WRDB.Project.clsApplicationSettings.UserName)
        '        End If

        '        If dataSetInfo.queryParameters IsNot Nothing Then
        '            For Each queryParameterInfo As QueryParameterInfo In dataSetInfo.queryParameters
        '                Dim valueExpression As String = queryParameterInfo.valueExpression
        '                Const starting As String = "=Parameters!"
        '                Const ending As String = ".Value"
        '                If Not valueExpression.StartsWith(starting) OrElse Not valueExpression.EndsWith(ending) Then
        '                    Throw New Exception("Can't parse query parameter expression: " & valueExpression)
        '                End If
        '                Dim parameterNameLen As Integer = valueExpression.Length - starting.Length - ending.Length
        '                Dim parameterName As String = valueExpression.Substring(starting.Length, parameterNameLen)
        '                Dim reportParameterValue As String = reportParams(parameterName).Values(0)

        '                If reportParameterValue Is Nothing Then
        '                    Throw New Exception("Report parameter " & parameterName & " has no value set")
        '                End If

        '                Select Case Project.DB.DatabaseType
        '                    Case WRDB.DBProviders.clsDB.enumDBType.SQL_Server, WRDB.DBProviders.clsDB.enumDBType.SQL_Server_CE
        '                        CType(command, SqlCommand).Parameters.AddWithValue(queryParameterInfo.parameterName, reportParameterValue)
        '                    Case Else
        '                        Throw New Exception("Parameters are allowed in the report design only for SQL Server databases.")
        '                        Return Nothing
        '                End Select
        '            Next
        '        End If

        '        Dim reader As Common.DbDataReader = command.ExecuteReader()
        '        Dim dataTable As New DataTable()
        '        dataTable.Load(reader)
        '        reader.Close()

        '        dataSetInfo.dataTable = dataTable
        '    End If
        '    Return dataSetInfo.dataTable
        'End Function

        ''' <summary>
        ''' Retrieve data from WRDB into datatable specified by the indicated dataset; if getting records for temp data table, replace that table name with last sql stmt
        ''' </summary>
        ''' <param name="dataSetName"></param>
        ''' <param name="reportParams"></param>
        ''' <param name="Project"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetData(dataSetName As String, reportParams As ReportParameterInfoCollection, Project As WRDB.Project.clsProject) As DataTable

            Dim dataSetInfo As DataSetInfo = m_dataSetDictionary(dataSetName)
            Dim dataSourceInfo As DataSourceInfo = m_dataSourceDictionary(dataSetInfo.dataSourceName)

            'normally extract data from current project; if want to get data from another database on the same server
            'just include prefix of database name in front of tablename like this: db_iarm.dbo.Datos_De_Telemetria

            If dataSourceInfo.connection Is Nothing Then
                dataSourceInfo.connection = Project.DB.Connection
            End If
            Project.DB.OpenConnection()
            Dim command As Common.DbCommand = dataSourceInfo.connection.CreateCommand()

            'SQL may contain comments which must be removed
            command.CommandText = ""
            For Each s In dataSetInfo.query.Split(vbNewLine)
                If String.IsNullOrEmpty(s) OrElse s.StartsWith("--") Then
                    s = ""
                Else
                    Dim j As Integer = s.IndexOf("--")
                    If j <> -1 Then s = s.Substring(0, j - 1).Trim
                End If
                command.CommandText &= " " & s.Replace(vbCr, "").Replace(vbLf, "")
            Next

            'command.CommandText = dataSetInfo.query

            'see if getting data from temp table; if so automatically replace that table with last sql stmt

            If command.CommandText.ToUpper.Contains(WRDB.Project.clsProject.TempData.ToUpper) Then
                command.CommandText = Microsoft.VisualBasic.Replace(command.CommandText, WRDB.Project.clsProject.TempData, "(" & LastSQL & ")", , , CompareMethod.Text)
            End If


            If dataSetInfo.queryParameters IsNot Nothing Then
                For Each queryParameterInfo As QueryParameterInfo In dataSetInfo.queryParameters
                    Dim valueExpression As String = queryParameterInfo.valueExpression
                    Const starting As String = "=Parameters!"
                    Const ending As String = ".Value"
                    If Not valueExpression.StartsWith(starting) OrElse Not valueExpression.EndsWith(ending) Then
                        Throw New Exception("Can't parse query parameter expression: " & valueExpression)
                    End If
                    Dim parameterNameLen As Integer = valueExpression.Length - starting.Length - ending.Length
                    Dim parameterName As String = valueExpression.Substring(starting.Length, parameterNameLen)

                    If reportParams(parameterName).Values.Count = 0 Then
                        Throw New Exception("Report parameter " & parameterName & " has no values in collection")
                    End If

                    Dim reportParameterValue As String = reportParams(parameterName).Values(0)

                    If reportParameterValue Is Nothing Then
                        Throw New Exception("Report parameter " & parameterName & " has no value set")
                    End If

                    Select Case Project.DB.DatabaseType
                        Case WRDB.DBProviders.clsDB.enumDBType.SQL_Server, WRDB.DBProviders.clsDB.enumDBType.SQL_Server_CE
                            Dim ReportParameterValueObj As Object = Nothing
                            Try
                                Select Case reportParams(parameterName).DataType
                                    Case ParameterDataType.Boolean : ReportParameterValueObj = CBool(reportParameterValue)
                                    Case ParameterDataType.DateTime : ReportParameterValueObj = CDate(reportParameterValue)
                                    Case ParameterDataType.Float : ReportParameterValueObj = CDbl(reportParameterValue)
                                    Case ParameterDataType.Integer : ReportParameterValueObj = CInt(reportParameterValue)
                                    Case ParameterDataType.String : ReportParameterValueObj = reportParameterValue
                                End Select
                            Catch ex As Exception
                                ReportParameterValueObj = DBNull.Value
                            End Try
                            CType(command, SqlCommand).Parameters.AddWithValue(queryParameterInfo.parameterName, ReportParameterValueObj)
                        Case Else
                            Throw New Exception("Parameters are allowed in the report design only for SQL Server databases.")
                            Return Nothing
                    End Select
                Next
            End If

            Dim reader As Common.DbDataReader = Nothing

            Try
                reader = command.ExecuteReader()
            Catch ex As Exception
                Throw New Exception("Error occurred while executing this SQL command: " & command.CommandText, ex)
                Return Nothing
            End Try

            Dim dataTable As New DataTable()
            dataTable.Load(reader)
            reader.Close()

            dataSetInfo.dataTable = DataTable

            Return dataSetInfo.dataTable


        End Function

        ' ''' <summary>
        ' ''' Retrieve data from WRDB into datatable specified by the indicated dataset; 
        ' ''' This version is called by WRDBWeb application where a dataset has already been filled with selected records
        ' ''' </summary>
        ' ''' <param name="dataSetName"></param>
        ' ''' <param name="reportParams"></param>
        ' ''' <param name="Project"></param>
        ' ''' <returns></returns>
        ' ''' <remarks>If report designed to pull records from TempData, will substitute SelectedData table from dataset</remarks>
        'Public Function GetData(dataSetName As String, reportParams As ReportParameterInfoCollection, Project As WRDB.Project.clsProject, WebDataSet As DataSet) As DataTable
        '    Dim dataSetInfo As DataSetInfo = m_dataSetDictionary(dataSetName)
        '    Dim dataSourceInfo As DataSourceInfo = m_dataSourceDictionary(dataSetInfo.dataSourceName)
        '    If dataSourceInfo.connection Is Nothing Then
        '        dataSourceInfo.connection = Project.DB.Connection
        '    End If
        '    Project.DB.OpenConnection()

        '    Dim command As Common.DbCommand = dataSourceInfo.connection.CreateCommand()

        '    'SQL may contain comments which must be removed
        '    command.CommandText = ""
        '    For Each s In dataSetInfo.query.Split(vbNewLine)
        '        If String.IsNullOrEmpty(s) OrElse s.StartsWith("--") Then
        '            s = ""
        '        Else
        '            Dim j As Integer = s.IndexOf("--")
        '            If j <> -1 Then s = s.Substring(0, j - 1).Trim
        '        End If
        '        command.CommandText &= " " & s.Replace(vbCr, "").Replace(vbLf, "")
        '    Next

        '    'command.CommandText = dataSetInfo.query.Replace(vbCrLf, " ").Replace(vbCr, " ").Replace(vbLf, " ")

        '    'add parameters which may be referenced in WHERE statement 
        '    For Each ReportParameterInfo As ReportParmInfo In ReportParameters
        '        Select Case Project.DB.DatabaseType
        '            Case WRDB.DBProviders.clsDB.enumDBType.SQL_Server, WRDB.DBProviders.clsDB.enumDBType.SQL_Server_CE
        '                If ReportParameterInfo.DataType = "DateTime" Then
        '                    'the value must be converted to a locale-independent format
        '                    CType(command, SqlCommand).Parameters.AddWithValue(ReportParameterInfo.Name, CDate(ReportParameterInfo.Value).ToString("MM/dd/yyyy"))
        '                Else
        '                    CType(command, SqlCommand).Parameters.AddWithValue(ReportParameterInfo.Name, ReportParameterInfo.Value)
        '                End If
        '            Case Else
        '                Throw New Exception("Parameters are allowed in the report design only for SQL Server databases.")
        '        End Select
        '    Next

        '    'see if getting data from temp table; if so automatically replace with selected records

        '    Dim lstSQL As New Generic.List(Of String)
        '    lstSQL.AddRange(command.CommandText.ToUpper.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries))

        '    'get tablename from SQL; assume it follows FROM keyword
        '    Dim tablename As String = ""
        '    'If lstSQL.Contains("FROM") Then
        '    '    tablename = lstSQL(lstSQL.IndexOf("FROM") + 1)
        '    'Else
        '    '    Throw New Exception("Unable to parse SQL string in report to determine table: " & command.CommandText)
        '    'End If

        '    'update 8/6/14 to handle advanced SQL statements that include subselects to interpret RCodes
        '    'there may be multiple FROM items in the statement; if so, use the one in which the table alias DTA follows the table name

        '    For s = 0 To lstSQL.Count - 1
        '        If lstSQL(s) = "FROM" Then
        '            If tablename = "" OrElse (s + 2 <= lstSQL.Count - 1 AndAlso lstSQL(s + 2) = "DTA") Then tablename = lstSQL(s + 1)
        '        End If
        '    Next

        '    'If tablename = "TEMP_WRDBDATA" And lstSQL.Contains("GROUP") Then tablename = "" 'so it will force execution of SQL statement below (NO! see notes below)

        '    'NOTE: report builder report must follow these rules:
        '    '  1. if query references table that is NOT TEMP_WRDBDATA then will ignore the records that user selected and take data directly from referenced table
        '    '  2. if query does reference TEMP_WRDBDATA, then WILL use selected records, however the query must not include any more support table fields than
        '    '     are included in the web query that creates the data table; also, the query must NOT expect to use GROUP BY in the SQL string and should instead
        '    '     use the grouping features of RB

        '    Dim dataTable As DataTable = Nothing

        '    'The procedure is hard to follow; it would appear that the Query property has a fully formed SQL statement that would be used to do an initial pull of the
        '    'data from the database, and then the dataset filters (which are set based on either dataset or report parameters) are applied separately as filters to the dataset
        '    'However, that is much too slow (will pull huge dataset from server); instead, will form SQL statement that uses SQL string in Query, and then do additional subselect
        '    'using the filters

        '    'OK, I added all this code to my original code (the one with ShowAllOwnerRecords argument used by WRDB) in an attempt to speed it up. It does make the query faster
        '    'but the reports still took 10 times longer to run in ASP.Net. I found that there is a bug in Framework 4 that caused this (see Application Notes.txt); an adjustment
        '    'to the web.config file speeds it up again. In fact, it is now 3x faster than WRDB version even with enhanced query code below. Therefore, I am disabling the advanced
        '    'code (by using If True...) to reduce possible future problems.

        '    '5/12/15: is actually much better to just write query in reportbuilder to reference parameters in where statement of query and leave filters empty, which removes
        '    'need for this complicated code so set Bypass... to true

        '    Dim BypassEnhancedQuery As Boolean = True 'set to true to use original SQL then later filter; false to use experimental enhanced query discussed above

        '    If BypassEnhancedQuery Or (dataSetInfo.queryFilters Is Nothing And Not lstSQL.Contains("WHERE")) Then 'no built in query filters or where conditions so is simply select of all records in table (based on sql in query text)
        '        'If WebDataSet.Tables.Contains(tablename) And Not lstSQL.Contains("WHERE") Then 'the table already exists, so use it
        '        If WebDataSet.Tables.Contains(tablename) Then 'the table already exists, so use it
        '            'note: this relies on calling method to create Temp_WRDBData table with all possible joined fields from Support tables
        '            dataTable = WebDataSet.Tables(tablename)
        '        Else 'not in dataset, get from database
        '            Dim reader As Common.DbDataReader = Nothing
        '            Try
        '                reader = command.ExecuteReader()
        '            Catch ex As Exception
        '                'if table not found don't want to trip error
        '                Debug.Assert(False, "Could not execute query")
        '                Return Nothing
        '            End Try
        '            dataTable = New DataTable()
        '            dataTable.Load(reader)
        '            reader.Close()
        '        End If
        '    Else 'because has parameters, MUST query from database
        '        If tablename = WRDB.Project.clsProject.TempData.ToUpper Then
        '            Throw New Exception("This report was designed with query parameters but also queries from the TempData table, which is not allowed in the WRDBWeb application.")
        '        End If
        '        'add WHERE conditions to SQL; expects that report design will simply be comparing table fields (in .expression) to specified values (in .values)
        '        'UPDATE 4/21/15; wanted to used enhanced report where parameter is an expression rather than a value (e.g., using DATEADD function)
        '        Dim where As String = ""
        '        If dataSetInfo.queryFilters IsNot Nothing Then
        '            For Each QueryFilter As QueryFilterInfo In dataSetInfo.queryFilters
        '                If Not QueryFilter.expression Like "=Fields*.Value" Then Throw New Exception("Filter expression must only represent a table field: " & QueryFilter.expression)

        '                Dim fieldname As String = QueryFilter.expression.Replace("=Fields!", "").Replace(".Value", "")

        '                Dim op As String = ""
        '                Select Case QueryFilter.operator
        '                    Case "LessThanOrEqual" : op = "<="
        '                    Case "LessThan" : op = "<"
        '                    Case "GreaterThanOrEqual" : op = ">="
        '                    Case "GreaterThan" : op = ">"
        '                    Case "Equal" : op = "="
        '                    Case "NotEqual" : op = "<>"
        '                    Case "Like" : op = "LIKE"
        '                    Case "In" : op = "IN"
        '                    Case "Between" : op = "BETWEEN"
        '                    Case Else
        '                        Throw New Exception("Unrecognized QueryFilter Operator: " & QueryFilter.operator)
        '                End Select

        '                If QueryFilter.values.Count <> 1 Then Throw New Exception("Filter values can only contain a single item: " & QueryFilter.values.Count)

        '                'if comparing to value, .values(0) contains "=Parameters!parmname.Value"; change to SQL "@parmname"
        '                'Dim value As String = "@" & QueryFilter.values(0).Replace("=Parameters!", "").Replace(".Value", "")

        '                '4/21/15: enhance to also parse an expression: .values(0) contains "=function(abc,def,Parameters!parmname.value); change to SQL "function(abc,def,@parmname)"
        '                'note: function in reportbuilder must have valid syntax in SQL Server; not sure how generally useful this will be

        '                Dim value As String = QueryFilter.values(0)
        '                value = value.Substring(1).Replace("Parameters!", "@").Replace(".Value", "")

        '                'must alter syntax in DateAdd function to be compatible with SQL Server:
        '                value = value.Replace("DateInterval.", "")

        '                Select Case op
        '                    Case "BETWEEN"
        '                        value = String.Format("@{0} AND @{1}",
        '                                              QueryFilter.values(0).Replace("=Parameters!", "").Replace(".Value", ""),
        '                                              QueryFilter.values(1).Replace("=Parameters!", "").Replace(".Value", ""))
        '                    Case "IN"
        '                        For Each p As String In QueryFilter.values
        '                            value = IIf(value = "", "(", ",") & "@" & p.Replace("=Parameters!", "").Replace(".Value", "")
        '                        Next
        '                        value &= ")"
        '                End Select

        '                where &= String.Format("{0} {1} {2} {3}", IIf(where = "", "WHERE", " AND"), fieldname, op, value)

        '            Next
        '        End If

        '        'revise SQL statement using subselect
        '        command.CommandText = String.Format("SELECT * FROM ({0}) AS Dta {1}", command.CommandText, where)

        '        'For Each ReportParameterInfo As ReportParmInfo In ReportParameters
        '        '    Select Case Project.DB.DatabaseType
        '        '        Case WRDB.DBProviders.clsDB.enumDBType.SQL_Server, WRDB.DBProviders.clsDB.enumDBType.SQL_Server_CE
        '        '            If ReportParameterInfo.DataType = "DateTime" Then
        '        '                'the value must be converted to a locale-independent format
        '        '                CType(command, SqlCommand).Parameters.AddWithValue(ReportParameterInfo.Name, CDate(ReportParameterInfo.Value).ToString("MM/dd/yyyy"))
        '        '            Else
        '        '                CType(command, SqlCommand).Parameters.AddWithValue(ReportParameterInfo.Name, ReportParameterInfo.Value)
        '        '            End If
        '        '        Case Else
        '        '            Throw New Exception("Parameters are allowed in the report design only for SQL Server databases.")
        '        '    End Select
        '        'Next

        '        Dim reader As Common.DbDataReader = Nothing

        '        Try

        '            reader = command.ExecuteReader()

        '        Catch ex As Exception

        '            Throw New Exception("Unable to execute SQL to retrieve data for report; note that not all ReportBuilder features are supported by WRDB:" & vbCrLf & command.CommandText, ex)
        '            Return Nothing

        '        End Try

        '        dataTable = New DataTable()
        '        dataTable.Load(reader)
        '        reader.Close()
        '    End If

        '    dataSetInfo.dataTable = dataTable
        '    Return dataSetInfo.dataTable
        'End Function

        ''' <summary>
        ''' Retrieve data from WRDB into datatable specified by the indicated dataset; 
        ''' This version is called by WRDBWeb application where trying to get valid values for parameters
        ''' </summary>
        Public Function GetData(dataSetName As String, WebDataSet As DataSet) As DataTable
            Dim dataSetInfo As DataSetInfo = m_dataSetDictionary(dataSetName)
            If dataSetInfo.dataTable Is Nothing Then
                Dim dataSourceInfo As DataSourceInfo = m_dataSourceDictionary(dataSetInfo.dataSourceName)
                If dataSourceInfo.connection Is Nothing Then
                    dataSourceInfo.connection = Project.DB.Connection
                    Project.DB.OpenConnection()
                End If
                Dim command As Common.DbCommand = dataSourceInfo.connection.CreateCommand()
                command.CommandText = dataSetInfo.query.Replace(vbCr, " ").Replace(vbLf, " ")

                'parse query to get tablename

                Dim lstSQL As New Generic.List(Of String)
                lstSQL.AddRange(command.CommandText.ToUpper.Split(New Char() {" "}, StringSplitOptions.RemoveEmptyEntries))

                'get tablename from SQL; assume it follows FROM keyword
                Dim tablename As String = ""
                If lstSQL.Contains("FROM") Then
                    tablename = lstSQL(lstSQL.IndexOf("FROM") + 1)
                Else
                    Throw New Exception("Unable to parse SQL string in report to determine table: " & command.CommandText)
                End If

                Dim dataTable As DataTable = Nothing

                If WebDataSet.Tables.Contains(tablename) And Not lstSQL.Contains("WHERE") Then 'get from dataset
                    dataTable = WebDataSet.Tables(tablename)
                Else 'not in dataset, get from database
                    Dim reader As Common.DbDataReader = Nothing
                    Try
                        reader = command.ExecuteReader()
                    Catch ex As Exception
                        'if table not found don't want to trip error
                        Debug.Assert(False, "Could not execute query")
                        Return Nothing
                    End Try
                    dataTable = New DataTable()
                    dataTable.Load(reader)
                    reader.Close()
                End If
                dataSetInfo.dataTable = dataTable
            End If
            Return dataSetInfo.dataTable
        End Function

        Public Sub Cleanup()
            For Each dataSourceInfo As DataSourceInfo In m_dataSourceDictionary.Values
                If dataSourceInfo.connection IsNot Nothing Then
                    dataSourceInfo.connection.Close()
                End If
            Next
            m_dataSourceDictionary = Nothing
            m_dataSetDictionary = Nothing
        End Sub
    End Class

    Public Class DataSetInfo
        Public dataSourceName As String
        Public query As String
        Public dataTable As DataTable
        Public queryParameters As IList(Of QueryParameterInfo)
        Public queryFilters As IList(Of QueryFilterInfo)

        Public Sub New(dataSourceName As String, query As String)
            Me.dataSourceName = dataSourceName
            Me.query = query
        End Sub
    End Class

    Public Class QueryParameterInfo
        Public parameterName As String
        Public valueExpression As String

        Public Sub New(parameterName As String, valueExpression As String)
            Me.parameterName = parameterName
            Me.valueExpression = valueExpression
        End Sub
    End Class

    Public Class QueryFilterInfo
        Public expression As String
        Public [operator] As String
        Public values As List(Of String)

        Public Sub New(expression As String, [operator] As String)
            Me.expression = expression
            Me.operator = [operator]
        End Sub
    End Class

    Public Class DataSourceInfo
        Public connectString As String
        Public connection As Common.DbConnection

        Public Sub New(connectString As String)
            Me.connectString = connectString
        End Sub
    End Class

    Public Class ReportParmInfo
        Public Name As String
        Public Prompt As String
        Public DataType As String
        Public Defaults As List(Of String)
        Public Datasets As List(Of ParmDatasetInfo)
        Public MultiValue As Boolean = False
        Public Parameters As Dictionary(Of String, String)
        Public Value As String 'the value assigned by the user
        Public Sub New(parameterName As String, prompt As String, dataType As String, Defaults As Generic.List(Of String), Datasets As List(Of ParmDatasetInfo), MultiValue As Boolean, Parameters As Dictionary(Of String, String))
            Me.Name = parameterName
            Me.Prompt = prompt
            Me.DataType = dataType
            Me.Defaults = Defaults
            Me.Datasets = Datasets
            Me.MultiValue = MultiValue
            Me.Parameters = Parameters
        End Sub
    End Class

    Public Class ParmDatasetInfo
        Public datasetName As String
        Public fields As New Dictionary(Of String, String)
        Public Sub New(datasetName As String, valueField As String, labelField As String)
            Me.datasetName = datasetName
            Me.fields.Add(valueField, labelField)
        End Sub
    End Class

End Namespace
